﻿using Model;
using DBUtility;//Please add references
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Dapper;
using Dapper.Mapper;

namespace DAL
{  
	/// <summary>
	/// 数据访问类:EmpoloyeeDAL
	/// </summary>
	public partial class EmployeeDAL
	{
        public IDbConnection Conn
        {
            get
            {
                return MySqlHelper.Conn;
            }
        }

        /// <summary>
        /// 新增一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int? Insert(EmployeeModel model)
        {
            try
            {
                using (Conn)
                {
                    string insertQuery = @"INSERT INTO ms_empoloyee (Id,DepartmentId,EmployeeNo,EmployeeName,EmployeeSex,EmployeeBirth,IsJob,Remarks) VALUES (@Id,@DepartmentId,@EmployeeNo,@EmployeeName,@EmployeeSex,@EmployeeBirth,@IsJob,@Remarks)";
                    var result = Conn.Execute(insertQuery, model);
                    return result;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(EmployeeModel model)
        {
            try
            {
                using (Conn)
                {
                    return Conn.Update(model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(string id)
        {
            try
            {
                using (Conn)
                {
                    EmployeeModel model = new EmployeeModel();
                    model.Id = id;
                    return Conn.Delete(model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(EmployeeModel model)
        {
            try
            {
                using (Conn)
                {
                    return Conn.Delete(model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据ID得到一个对象实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public EmployeeModel Get(string id)
        {
            try
            {
                using (Conn)
                {
                    return Conn.Get<EmployeeModel>(id);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询多条数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<EmployeeModel> GetList()
        {
            try
            {
                using (Conn)
                {
                    return Conn.GetList<EmployeeModel>();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 根据EmployeeNo查询数据
        /// </summary>
        /// <returns></returns>
        public EmployeeModel GetModelByEmployeeNo(string employeeNo)
        {
            try
            {
                using (Conn)
                {
                    string insertQuery = @"select * from ms_empoloyee where EmployeeNo=@EmployeeNo";
                    var para = new DynamicParameters();
                    para.Add("EmployeeNo", employeeNo);
                    var result = Conn.QuerySingleOrDefault<EmployeeModel>(insertQuery, para);
                    return result;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 根据Id和编码查询数据
        /// </summary>
        /// <returns></returns>
        public int IsExistByIdAndEmployeeNo(string Id, string EmployeeNo)
        {
            try
            {
                using (Conn)
                {
                    string query = "select * from ms_empoloyee where EmployeeNo=@EmployeeNo and Id<>@Id";
                    var para = new DynamicParameters();
                    para.Add("Id", Id);
                    para.Add("EmployeeNo", EmployeeNo);
                    EmployeeModel model = Conn.QuerySingleOrDefault<EmployeeModel>(query, para);
                    if (model != null)
                    {
                        return 1;
                    }
                    else
                    {
                        return -1;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 根据Id和编码查询数据
        /// </summary>
        /// <returns></returns>
        public int IsExistByEmployeeNo(string EmployeeNo)
        {
            try
            {
                using (Conn)
                {
                    string query = "select * from ms_empoloyee where EmployeeNo=@EmployeeNo";
                    var para = new DynamicParameters();
                    para.Add("EmployeeNo", EmployeeNo);
                    EmployeeModel model = Conn.QuerySingleOrDefault<EmployeeModel>(query, para);
                    if (model != null)
                    {
                        return 1;
                    }
                    else
                    {
                        return -1;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="employeeNo"></param>
        /// <param name="employeeName"></param>
        /// <returns></returns>
        public IEnumerable<EmployeeModel> GetModelListByNoAndName(string employeeNo, string employeeName)
        {
            try
            {
                using (Conn)
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("select * from ms_empoloyee where 1=1");
                    var para = new DynamicParameters();
                    if (!string.IsNullOrEmpty(employeeNo))
                    {
                        strSql.Append(" and EmployeeNo like @EmployeeNo");
                        para.Add("EmployeeNo", "%" + employeeNo + "%");
                    }
                    if (!string.IsNullOrEmpty(employeeName))
                    {
                        strSql.Append(" and EmployeeName like @EmployeeName");
                        para.Add("EmployeeName", "%" + employeeName + "%");
                    }
                    strSql.Append(" Order by DepartmentId,EmployeeNo asc ");
                    return Conn.Query<EmployeeModel>(strSql.ToString(), para);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EmployeeModel DataRowToModel(DataRow row)
        {
            EmployeeModel model = new EmployeeModel();
            if (row != null)
            {
                if (row["Id"] != null)
                {
                    model.Id = row["Id"].ToString();
                }
                if (row["DepartmentId"] != null)
                {
                    model.DepartmentId = row["DepartmentId"].ToString();
                }
                if (row["EmployeeNo"] != null)
                {
                    model.EmployeeNo = row["EmployeeNo"].ToString();
                }
                if (row["EmployeeName"] != null)
                {
                    model.EmployeeName = row["EmployeeName"].ToString();
                }
                if (row["EmployeeSex"] != null)
                {
                    model.EmployeeSex = row["EmployeeSex"].ToString();
                }
                if (row["EmployeeBirth"] != null && row["EmployeeBirth"].ToString() != "")
                {
                    model.EmployeeBirth = DateTime.Parse(row["EmployeeBirth"].ToString());
                }
                if (row["IsJob"] != null && row["IsJob"].ToString() != "")
                {
                    if (row["IsJob"].ToString() == "True")
                    {
                        model.IsJob = 1;
                    }
                    else
                    {
                        model.IsJob = 0;
                    }
                }
                if (row["Remarks"] != null)
                {
                    model.Remarks = row["Remarks"].ToString();
                }
            }
            return model;
        }
	}
}


